package com.situ.company.score.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.company.score.model.ScoreModel;
import com.situ.company.util.JDBCUtil;

public class ScoreDao {
	
	private static String table = "st_company.score";
	private static String cols = "empcode,procode,score";
	private Connection conn = null;
	private PreparedStatement ps = null;
	
	public Integer insert(ScoreModel model) {
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(table).append(" (").append(cols).append(") values(?,?,?)");
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, model.getEmpcode());
			ps.setString(2, model.getProcode());
			ps.setString(3, model.getScore());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	public Integer delete(ScoreModel model) {
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(table);// where code = ?
		List<Object> vals = appendWhere(sql, model);
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			return ps.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	public List<ScoreModel> selectList(ScoreModel model) {
		List<ScoreModel> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select ");
		sql.append(cols);
		sql.append(",(select e.name from st_company.employee e where e.code = empcode) empname");
		sql.append(",(select p.name from st_company.project p where p.code = procode) proname");
		sql.append(" from ").append(table);
		List<Object> vals = appendWhere(sql, model);
		System.out.println(sql);

		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				ScoreModel mod = new ScoreModel();
				mod.setEmpcode(rs.getString("empcode"));
				mod.setEmpname(rs.getString("empname"));
				mod.setProcode(rs.getString("procode"));
				mod.setProname(rs.getString("proname"));
				mod.setScore(rs.getString("score"));
				list.add(mod);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return list;
	}
	
	private List<Object> appendWhere(StringBuffer sql, ScoreModel model) {
		sql.append(" where 1=1");
		List<Object> objs = new ArrayList<Object>();
		String empcode = model.getEmpcode();
		if (empcode != null && !empcode.trim().isEmpty()) {// !"".equals(code.trim())
			sql.append(" and empcode like ?");
			objs.add(empcode);
		}
		String procode = model.getProcode();
		if (procode != null && !procode.trim().isEmpty()) {
			sql.append(" and procode like ?");
			objs.add(procode);
		}
		if (model.isPageOn()) {
			sql.append(" limit ?,? ");
			objs.add(model.getRowStart());
			objs.add(model.getRowCount());
		}
		return objs;
	}

	public Integer update(ScoreModel model,List<Object> tobjs) {
		StringBuffer sql = new StringBuffer("update ");
		sql.append(table);
		List<Object> vals = appendSet(sql, model,tobjs);
		System.out.println(sql);
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	private List<Object> appendSet(StringBuffer sql, ScoreModel model,List<Object> tobjs) {
		sql.append(" set id=id ");
		List<Object> objs = new ArrayList<Object>();
		String empcode = model.getEmpcode();
		if (empcode != null) {
			sql.append(",empcode = ? ");
			objs.add(empcode);
		}
		String procode = model.getProcode();
		if (procode != null) {
			sql.append(",procode = ? ");
			objs.add(procode);
		}
		String score = model.getScore();
		if (score != null) {
			sql.append(",score = ? ");
			objs.add(score);
		}		
		sql.append(" where empcode = ? and procode = ?");
		objs.add(tobjs.get(0));
		objs.add(tobjs.get(1));
		return objs;
	}
	
	public int selectCount(ScoreModel model) {

		StringBuffer sql = new StringBuffer("select count(id) from ");
		sql.append(table);
		List<Object> vals = appendWhere(sql, model);

		System.out.println(sql);

		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));	
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return 0;
	}
	
}
